package cn.fintecher.pangolin.service.report.service;

import cn.fintecher.pangolin.common.annotation.ReportAnno;
import cn.fintecher.pangolin.common.enums.BaseSelect;
import cn.fintecher.pangolin.common.enums.ReportType;
import cn.fintecher.pangolin.common.enums.TaskBoxStatus;
import cn.fintecher.pangolin.common.enums.TaskBoxType;
import cn.fintecher.pangolin.common.model.DepartmentModel;
import cn.fintecher.pangolin.common.model.TaskBoxModel;
import cn.fintecher.pangolin.common.model.UploadFile;
import cn.fintecher.pangolin.common.model.UserModel;
import cn.fintecher.pangolin.common.utils.ExportDataToExcelUtil;
import cn.fintecher.pangolin.common.utils.ZWDateUtil;
import cn.fintecher.pangolin.entity.elastic.BehavioralProcess;
import cn.fintecher.pangolin.entity.elastic.DailyCollectionStatisticsRecord;
import cn.fintecher.pangolin.entity.elastic.PrincipalPayment;
import cn.fintecher.pangolin.entity.elastic.UserPayment;
import cn.fintecher.pangolin.entity.mysql.QUser;
import cn.fintecher.pangolin.entity.mysql.User;
import cn.fintecher.pangolin.service.report.elastic.BehavioralProcessElastic;
import cn.fintecher.pangolin.service.report.elastic.DailyCollectionStatisticsRecordRepository;
import cn.fintecher.pangolin.service.report.elastic.PrincipalPaymentElastic;
import cn.fintecher.pangolin.service.report.elastic.UserPaymentElastic;
import cn.fintecher.pangolin.service.report.repository.UserRepository;
import cn.fintecher.pangolin.service.report.request.DailyCollectionStatisticsRequest;
import cn.fintecher.pangolin.service.report.request.ReportRequest;
import cn.fintecher.pangolin.service.report.task.BehavioralProcessTask;
import cn.fintecher.pangolin.service.report.task.PrincipalPaymentTask;
import cn.fintecher.pangolin.service.report.task.UserPaymentTask;
import com.google.common.collect.Lists;
import com.querydsl.core.BooleanBuilder;
import org.apache.commons.collections4.IterableUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.stream.Collectors;

/**
 * @Auther: xiaqun
 * @Description:
 * @Date: 9:13 2019/12/11
 */

@Service("reportService")
public class ReportService {
    @Autowired
    private BaseService baseService;

    @Autowired
    private BehavioralProcessTask behavioralProcessTask;

    @Autowired
    private BehavioralProcessElastic behavioralProcessElastic;

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private PrincipalPaymentElastic principalPaymentElastic;

    @Autowired
    private PrincipalPaymentTask principalPaymentTask;

    @Autowired
    private DailyCollectionStatisticsRecordRepository dailyCollectionStatisticsRecordRepository;

    @Autowired
    private UserPaymentTask userPaymentTask;

    @Autowired
    private UserPaymentElastic userPaymentElastic;

    /**
     * 查询行为过程监控表
     */
    public List<BehavioralProcess> getBehavioralProcess(ReportRequest request, String token) {
        UserModel userModel = baseService.getUserByToken(token);
        if (userModel.getIsManage().equals(BaseSelect.NO)) {
            return new ArrayList<>();
        }
        DepartmentModel departmentModel = baseService.getDepartmentByToken(token);
        List<BehavioralProcess> processList = new ArrayList<>();
        if (request.getType().equals(ReportType.REAL_TIME)) { //实时
            BooleanBuilder builder = new BooleanBuilder();
            QUser qUser = QUser.user;
            builder.and(qUser.department.departCode.like(departmentModel.getDepartCode() + "%"));
            if (StringUtils.isNotBlank(request.getDepartCode())) {
                builder.and(qUser.department.departCode.like(request.getDepartCode() + "%"));
            }
            if (StringUtils.isNotBlank(request.getUserIds())) {
                List<String> userIds = processUserIds(request.getUserIds());
                builder.and(qUser.id.in(userIds));
            }
            Iterable<User> userIterable = userRepository.findAll(builder);
            List<User> users = IterableUtils.toList(userIterable);
            List<CompletableFuture<BehavioralProcess>> list = new ArrayList<>();
            for (User user : users) {
                CompletableFuture<BehavioralProcess> future = behavioralProcessTask.doTask(user, ZWDateUtil.getNowDate());
                list.add(future);
            }
            processList = BaseService.processList(list);
        } else { //历史
            BoolQueryBuilder builder = new BoolQueryBuilder();
            builder.must(QueryBuilders.wildcardQuery("departCode.keyword", departmentModel.getDepartCode() + "*"));
            if (StringUtils.isNotBlank(request.getDepartCode())) {
                builder.must(QueryBuilders.wildcardQuery("departCode.keyword", request.getDepartCode() + "*"));
            }
            if (StringUtils.isNotBlank(request.getUserIds())) {
                List<String> userIds = processUserIds(request.getUserIds());
                builder.must(QueryBuilders.termsQuery("userId.keyword", userIds));
            }
            if (Objects.nonNull(request.getStartDate())) {
                builder.must(QueryBuilders.rangeQuery("reportDate").gte(request.getStartDate().getTime()));
            }
            if (Objects.nonNull(request.getEndDate())) {
                builder.must(QueryBuilders.rangeQuery("reportDate").lt(new DateTime(request.getEndDate()).plusDays(1).toDate().getTime()));
            }
            Iterable<BehavioralProcess> iterable = behavioralProcessElastic.search(builder);
            List<BehavioralProcess> list = IterableUtils.toList(iterable);
            processList.addAll(list);
        }
        if (processList.isEmpty()) {
            return processList;
        } else {
            return processList.stream().sorted(Comparator.comparing(BehavioralProcess::getDepartId).thenComparing(BehavioralProcess::getUserId).thenComparing(BehavioralProcess::getReportDate)).collect(Collectors.toList());
        }
    }

    /**
     * 查询委托方回款报表
     */
    public List<PrincipalPayment> getPrincipalPayment(ReportRequest request) {
        BoolQueryBuilder builder = new BoolQueryBuilder();
        builder.must(QueryBuilders.matchPhraseQuery("isStatistics.keyword", BaseSelect.YES.name()));
        if (StringUtils.isNotBlank(request.getPrincipalId())) {
            builder.must(QueryBuilders.matchPhraseQuery("principalId.keyword", request.getPrincipalId()));
        }
        if (StringUtils.isNotBlank(request.getBatchNumber())) {
            builder.must(QueryBuilders.matchPhraseQuery("batchNumber.keyword", request.getBatchNumber()));
        }
        if (Objects.nonNull(request.getStartDate())) {
            builder.must(QueryBuilders.rangeQuery("delegationDate").gte(request.getStartDate().getTime()));
        }
        if (Objects.nonNull(request.getEndDate())) {
            builder.must(QueryBuilders.rangeQuery("delegationDate").lt(new DateTime(request.getEndDate()).plusDays(1).toDate().getTime()));
        }
        Iterable<PrincipalPayment> iterable = principalPaymentElastic.search(builder);
        List<PrincipalPayment> principalPayments = IterableUtils.toList(iterable);
        if (principalPayments.isEmpty()) {
            return new ArrayList<>();
        } else {
            List<CompletableFuture<PrincipalPayment>> list = new ArrayList<>();
            for (PrincipalPayment principalPayment : principalPayments) {
                CompletableFuture<PrincipalPayment> future = principalPaymentTask.doTask(principalPayment);
                list.add(future);
            }
            List<PrincipalPayment> payments = BaseService.processList(list);
            return payments.stream().sorted(Comparator.comparing(PrincipalPayment::getPrincipalId).thenComparing(PrincipalPayment::getBatchNumber)).collect(Collectors.toList());
        }
    }

    /**
     * 导出行为过程监控表
     */
    @Async
    public void exportBehavioralProcess(ReportRequest request, String token) {
        UserModel userModel = baseService.getUserByToken(token);
        TaskBoxModel model = baseService.getTaskBox(TaskBoxType.EXPORT, "行为过程监控表导出", userModel);
        baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.UN_FINISH, null, null, userModel, ZWDateUtil.getNowDateTime(), null, "行为过程监控表导出");
        String fileName = "行为过程监控表".concat(ZWDateUtil.fomratterDate(new Date(), "yyyyMMddHHmmss")).concat(".xlsx");
        String filePath = FileUtils.getTempDirectoryPath().concat(fileName);
        List<BehavioralProcess> list = getBehavioralProcess(request, token);
        if (list.isEmpty()) {
            String fileId = baseService.generateTxt(Collections.singletonList("行为过程监控表无数据"), Long.toString(new Date().getTime()));
            baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "行为过程监控表导出", userModel, null, ZWDateUtil.getNowDateTime(), "行为过程监控表导出");
        } else {
            try {
                if (request.getType().equals(ReportType.REAL_TIME)) {
                    exportReport(filePath, "行为过程监控表", 0, 0, list, BehavioralProcess.class);
                } else {
                    exportHistoryReport(filePath, list, request.getStartDate(), request.getEndDate());
                }
                UploadFile file = baseService.getFileUrl(filePath);
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FINISHED, file.getId(), file.getOriginalName(), userModel, null, ZWDateUtil.getNowDateTime(), "行为过程监控表导出");
            } catch (Exception e) {
                e.printStackTrace();
                String fileId = baseService.generateTxt(Collections.singletonList("行为过程监控表导出异常"), Long.toString(new Date().getTime()));
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "行为过程监控表导出", userModel, null, ZWDateUtil.getNowDateTime(), "行为过程监控表导出");
            }
        }
    }

    /**
     * 导出委托方回款报表
     */
    @Async
    public void exportPrincipalPayment(ReportRequest request, String token) {
        UserModel userModel = baseService.getUserByToken(token);
        TaskBoxModel model = baseService.getTaskBox(TaskBoxType.EXPORT, "委托方回款报表导出", userModel);
        baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.UN_FINISH, null, null, userModel, ZWDateUtil.getNowDateTime(), null, "委托方回款报表导出");
        String fileName = "委托方回款表".concat(ZWDateUtil.fomratterDate(new Date(), "yyyyMMddHHmmss")).concat(".xlsx");
        String filePath = FileUtils.getTempDirectoryPath().concat(fileName);
        List<PrincipalPayment> list = getPrincipalPayment(request);
        if (list.isEmpty()) {
            String fileId = baseService.generateTxt(Collections.singletonList("委托方回款表无数据"), Long.toString(new Date().getTime()));
            baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "委托方回款报表导出", userModel, null, ZWDateUtil.getNowDateTime(), "委托方回款报表导出");
        } else {
            try {
                exportReport(filePath, "委托方回款表", 0, 0, list, PrincipalPayment.class);
                UploadFile file = baseService.getFileUrl(filePath);
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FINISHED, file.getId(), file.getOriginalName(), userModel, null, ZWDateUtil.getNowDateTime(), "委托方回款报表导出");
            } catch (Exception e) {
                e.printStackTrace();
                String fileId = baseService.generateTxt(Collections.singletonList("委托方回款表导出异常"), Long.toString(new Date().getTime()));
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "委托方回款报表导出", userModel, null, ZWDateUtil.getNowDateTime(), "委托方回款报表导出");
            }
        }
    }

    /**
     * 导出excel报表
     *
     * @param filePath  文件路径
     * @param titleName 报表标题
     * @param startCol  内容开始列
     * @param startRow  内容开始行
     * @param dataList  数据集合
     */
    private static <T> void exportReport(String filePath, String titleName, int startCol, int startRow, List<T> dataList, Class<T> clazz) {
        if (dataList.isEmpty()) {
            return;
        }
        FileOutputStream fos = null;
        SXSSFWorkbook wb = null;
        try {
            fos = new FileOutputStream(filePath);
            wb = new SXSSFWorkbook(ExportDataToExcelUtil.ROW_MEMORY);
            CellStyle headStyle = ExportDataToExcelUtil.headCellType(wb);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font f = wb.createFont();
            f.setFontHeightInPoints((short) 12);
            f.setBold(true);
            f.setColor(new HSSFColor.WHITE().getIndex());
            headStyle.setFont(f);
            headStyle.setFillForegroundColor(new HSSFColor.ROYAL_BLUE().getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            CellStyle dataStyle = ExportDataToExcelUtil.dataCellType(wb);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Sheet sheet = wb.createSheet(titleName);
            int endCol = startCol;
            Field[] fields = clazz.getDeclaredFields();
            List<Field> fieldList = new ArrayList<>();
            for (Field field : fields) {
                if (Objects.nonNull(field.getAnnotation(ReportAnno.class))) {
                    fieldList.add(field);
                    endCol++;
                }
            }
            //创建表名
            Row titleNameRow = sheet.createRow(startRow);
            Cell titleNameCell = titleNameRow.createCell(startCol);
            titleNameCell.setCellValue(titleName);
            titleNameCell.setCellStyle(headStyle);
            //合并表名
            CellRangeAddress titleRegion = new CellRangeAddress(startRow, startRow, startCol, endCol - 1);
            sheet.addMergedRegion(titleRegion);
            //创建表头
            List<Integer> merge = new ArrayList<>();
            Row titleRow = sheet.createRow(startRow + 1);
            for (Field field : fieldList) {
                int sort = field.getAnnotation(ReportAnno.class).sort();
                String name = field.getAnnotation(ReportAnno.class).name();
                boolean isMerge = field.getAnnotation(ReportAnno.class).isMerge();
                if (isMerge) {
                    merge.add(sort - 1);
                }
                Cell titleCell = titleRow.createCell(startCol + sort - 1);
                titleCell.setCellType(CellType.STRING);
                titleCell.setCellStyle(headStyle);
                titleCell.setCellValue(name);
            }
            //写入数据
            int rowNumber = startRow + 2;
            for (T t : dataList) {
                Row dataRow = sheet.createRow(rowNumber);
                for (Field field : fieldList) {
                    field.setAccessible(true);
                    int sort = field.getAnnotation(ReportAnno.class).sort();
                    String value;
                    if (field.getType().equals(Date.class)) {
                        value = ZWDateUtil.fomratterDate((Date) field.get(t), "yyyy-MM-dd");
                    } else if (field.getType().equals(Double.class) || field.getType().equals(double.class)) {
                        int scale = field.getAnnotation(ReportAnno.class).scale();
                        boolean isRate = field.getAnnotation(ReportAnno.class).isRate();
                        Double d = (double) field.get(t);
                        BigDecimal bd = new BigDecimal(d).setScale(scale, BigDecimal.ROUND_HALF_UP);
                        if (isRate) {
                            value = bd.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString() + "%";
                        } else {
                            value = bd.toString();
                        }

                    } else if (field.getType().equals(Float.class) || field.getType().equals(float.class)) {
                        boolean isRate = field.getAnnotation(ReportAnno.class).isRate();
                        float aFloat = (float) field.get(t);
                        if (isRate) {
                            if (aFloat == 0f) {
                                value = aFloat * 100 + "%";
                            } else {
                                BigDecimal bigDecimal = new BigDecimal((double) aFloat).setScale(4, BigDecimal.ROUND_HALF_UP);
                                value = bigDecimal.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString() + "%";
                            }
                        } else {
                            value = Float.toString(aFloat);
                        }
                    } else {
                        value = field.get(t).toString();
                    }
                    Cell dataCell = dataRow.createCell(startCol + sort - 1);
                    dataCell.setCellType(CellType.STRING);
                    dataCell.setCellValue(value);
                    dataCell.setCellStyle(dataStyle);
                }
                rowNumber++;
            }
            //合并数据
            if (!merge.isEmpty()) {
                for (Integer integer : merge) {
                    String value = "";
                    int mergeStartRow = startRow + 2;
                    int endRow = sheet.getLastRowNum();
                    for (int i = startRow + 2; i <= endRow; i++) {
                        Row row = sheet.getRow(i);
                        String cellValue = row.getCell(integer).getStringCellValue();
                        if (i > startRow + 2 && i < endRow) {
                            if (!value.equals(cellValue)) {
                                if (mergeStartRow < (i - 1)) {
                                    CellRangeAddress dataRegion = new CellRangeAddress(mergeStartRow, i - 1, integer, integer);
                                    sheet.addMergedRegion(dataRegion);
                                }
                                mergeStartRow = i;
                            }
                        } else if (i == endRow) {
                            if (value.equals(cellValue)) {
                                CellRangeAddress dataRegion = new CellRangeAddress(mergeStartRow, i, integer, integer);
                                sheet.addMergedRegion(dataRegion);
                            }
                        }
                        value = cellValue;
                    }
                }
            }
            wb.write(fos);
            fos.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (Objects.nonNull(fos)) {
                    fos.close();
                }
                if (Objects.nonNull(wb)) {
                    wb.close();
                }
                dataList.clear();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public List<DailyCollectionStatisticsRecord> dailyCollectionStatistics(DailyCollectionStatisticsRequest dailyCollectionStatisticsRequest) {
        BoolQueryBuilder boolQueryBuilder = dailyCollectionStatisticsRequest.generateBoolQueryBuilder();
        return Lists.newArrayList(dailyCollectionStatisticsRecordRepository.search(boolQueryBuilder)).stream()
                .sorted(Comparator.comparing(DailyCollectionStatisticsRecord::getDepartmentCode).thenComparing(DailyCollectionStatisticsRecord::getCollectorId)
                        .thenComparing(DailyCollectionStatisticsRecord::getStatisticsDate)).collect(Collectors.toList());
    }

    @Async
    public void exportDailyStatistics(DailyCollectionStatisticsRequest request, String token) {
        UserModel userModel = baseService.getUserByToken(token);
        TaskBoxModel model = baseService.getTaskBox(TaskBoxType.EXPORT, "导出每日催收统计", userModel);
        baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.UN_FINISH, null, null, userModel, ZWDateUtil.getNowDateTime(), null, "每日催收统计导出");
        String fileName = "每日催收统计".concat(ZWDateUtil.fomratterDate(new Date(), "yyyyMMddHHmmss")).concat(".xlsx");
        String filePath = FileUtils.getTempDirectoryPath().concat(fileName);
        List<DailyCollectionStatisticsRecord> list = Lists.newArrayList(dailyCollectionStatisticsRecordRepository.search(request.generateBoolQueryBuilder()));
        if (list.isEmpty()) {
            String fileId = baseService.generateTxt(Collections.singletonList("每日催收统计"), Long.toString(new Date().getTime()));
            baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "导出每日催收统计", userModel, null, ZWDateUtil.getNowDateTime(), "每日催收统计导出");
        } else {
            List<DailyCollectionStatisticsRecord> sorted = list.stream().sorted(Comparator.comparing(DailyCollectionStatisticsRecord::getDepartmentCode).thenComparing(DailyCollectionStatisticsRecord::getCollectorId)).collect(Collectors.toList());
            try {
                exportReport(filePath, "每日催收统计导出", 0, 0, sorted, DailyCollectionStatisticsRecord.class);
                UploadFile file = baseService.getFileUrl(filePath);
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FINISHED, file.getId(), file.getOriginalName(), userModel, null, ZWDateUtil.getNowDateTime(), "每日催收统计导出");
            } catch (Exception e) {
                e.printStackTrace();
                String fileId = baseService.generateTxt(Collections.singletonList("每日催收统计导出异常"), Long.toString(new Date().getTime()));
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "每日催收统计导出", userModel, null, ZWDateUtil.getNowDateTime(), "每日催收统计导出");
            }
        }
    }

    /**
     * 查询催收员回款报表
     */
    public List<UserPayment> getUserPayment(ReportRequest request, String token) {
        UserModel userModel = baseService.getUserByToken(token);
        if (userModel.getIsManage().equals(BaseSelect.NO)) {
            return new ArrayList<>();
        }
        DepartmentModel departmentModel = baseService.getDepartmentByToken(token);
        List<UserPayment> userPayments = new ArrayList<>();
        if (request.getType().equals(ReportType.REAL_TIME)) { //实时
            BooleanBuilder builder = new BooleanBuilder();
            QUser qUser = QUser.user;
            builder.and(qUser.department.departCode.like(departmentModel.getDepartCode() + "%"));
            if (StringUtils.isNotBlank(request.getDepartCode())) {
                builder.and(qUser.department.departCode.like(request.getDepartCode() + "%"));
            }
            if (StringUtils.isNotBlank(request.getUserIds())) {
                List<String> userIds = processUserIds(request.getUserIds());
                builder.and(qUser.id.in(userIds));
            }
            Iterable<User> userIterable = userRepository.findAll(builder);
            List<User> users = IterableUtils.toList(userIterable);
            List<CompletableFuture<UserPayment>> list = new ArrayList<>();
            for (User user : users) {
                CompletableFuture<UserPayment> future = userPaymentTask.doTask(user, ZWDateUtil.getNowDate());
                list.add(future);
            }
            userPayments = BaseService.processList(list);
        } else { //历史
            BoolQueryBuilder builder = new BoolQueryBuilder();
            builder.must(QueryBuilders.wildcardQuery("departCode.keyword", departmentModel.getDepartCode() + "*"));
            if (StringUtils.isNotBlank(request.getDepartCode())) {
                builder.must(QueryBuilders.wildcardQuery("departCode.keyword", request.getDepartCode() + "*"));
            }
            if (StringUtils.isNotBlank(request.getUserIds())) {
                List<String> userIds = processUserIds(request.getUserIds());
                builder.must(QueryBuilders.termsQuery("userId.keyword", userIds));
            }
            if (Objects.nonNull(request.getStartDate())) {
                builder.must(QueryBuilders.rangeQuery("reportDate").gte(request.getStartDate().getTime()));
            }
            if (Objects.nonNull(request.getEndDate())) {
                builder.must(QueryBuilders.rangeQuery("reportDate").lt(new DateTime(request.getEndDate()).plusDays(1).toDate().getTime()));
            }
            Iterable<UserPayment> iterable = userPaymentElastic.search(builder);
            List<UserPayment> list = IterableUtils.toList(iterable);
            userPayments.addAll(list);
        }
        if (userPayments.isEmpty()) {
            return userPayments;
        } else {
            return userPayments.stream().sorted(Comparator.comparing(UserPayment::getDepartId).thenComparing(UserPayment::getUserId).thenComparing(UserPayment::getReportDate)).collect(Collectors.toList());
        }
    }

    /**
     * 导出催收员回款报表
     */
    @Async
    public void exportUserPayment(ReportRequest request, String token) {
        UserModel userModel = baseService.getUserByToken(token);
        TaskBoxModel model = baseService.getTaskBox(TaskBoxType.EXPORT, "催收员回款报表导出", userModel);
        baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.UN_FINISH, null, null, userModel, ZWDateUtil.getNowDateTime(), null, "催收员回款报表导出");
        String fileName = "催收员回款报表".concat(ZWDateUtil.fomratterDate(new Date(), "yyyyMMddHHmmss")).concat(".xlsx");
        String filePath = FileUtils.getTempDirectoryPath().concat(fileName);
        List<UserPayment> list = getUserPayment(request, token);
        if (list.isEmpty()) {
            String fileId = baseService.generateTxt(Collections.singletonList("催收员回款报表无数据"), Long.toString(new Date().getTime()));
            baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "催收员回款报表导出", userModel, null, ZWDateUtil.getNowDateTime(), "催收员回款报表导出");
        } else {
            try {
                exportReport(filePath, "催收员回款报表", 0, 0, list, UserPayment.class);
                UploadFile file = baseService.getFileUrl(filePath);
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FINISHED, file.getId(), file.getOriginalName(), userModel, null, ZWDateUtil.getNowDateTime(), "催收员回款报表导出");
            } catch (Exception e) {
                e.printStackTrace();
                String fileId = baseService.generateTxt(Collections.singletonList("催收员回款报表导出异常"), Long.toString(new Date().getTime()));
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, fileId, "催收员回款报表导出", userModel, null, ZWDateUtil.getNowDateTime(), "催收员回款报表导出");
            }
        }
    }

    /**
     * 处理用户ID字符串集合
     */
    private List<String> processUserIds(String userIds) {
        String[] strings = userIds.split(",");
        return Arrays.asList(strings);
    }

    /**
     * 导出行为过程监控表历史表
     */
    private void exportHistoryReport(String filePath, List<BehavioralProcess> list, Date startDate, Date endDate) {
        if (list.isEmpty()) {
            return;
        }
        FileOutputStream fos = null;
        SXSSFWorkbook wb = null;
        try {
            fos = new FileOutputStream(filePath);
            wb = new SXSSFWorkbook(ExportDataToExcelUtil.ROW_MEMORY);
            CellStyle headStyle = ExportDataToExcelUtil.headCellType(wb);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font f = wb.createFont();
            f.setFontHeightInPoints((short) 12);
            f.setBold(true);
            f.setColor(new HSSFColor.WHITE().getIndex());
            headStyle.setFont(f);
            headStyle.setFillForegroundColor(new HSSFColor.ROYAL_BLUE().getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            CellStyle dataStyle = ExportDataToExcelUtil.dataCellType(wb);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Sheet sheet = wb.createSheet("行为过程监控表");
            //创建表名
            Row titleNameRow = sheet.createRow(0);
            Cell titleNameCell = titleNameRow.createCell(0);
            titleNameCell.setCellValue("行为过程监控表");
            titleNameCell.setCellStyle(headStyle);
            //合并表名
            CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 7);
            sheet.addMergedRegion(titleRegion);
            //创建表头
            Row titleRow = sheet.createRow(1);
            String[] title = {"部门", "催收员", "日期", "在催案件量", "案件触碰量", "触碰率", "行动代码量", "渗透率"};
            for (int i = 0; i < 8; i++) {
                Cell titleCell = titleRow.createCell(i);
                titleCell.setCellType(CellType.STRING);
                titleCell.setCellStyle(headStyle);
                titleCell.setCellValue(title[i]);
            }
            //写入数据
            int rowNumber = 2;
            String userId = list.get(0).getUserId();
            int collectingNumber = 0;
            int touchNumber = 0;
            int codeNumber = 0;
            BehavioralProcess history = new BehavioralProcess();
            for (BehavioralProcess process : list) {
                if (userId.equals(process.getUserId())) {
                    history = process;
                    collectingNumber += Objects.isNull(process.getCollectingNumber()) ? 0 : process.getCollectingNumber();
                    touchNumber += Objects.isNull(process.getTouchNumber()) ? 0 : process.getTouchNumber();
                    codeNumber += Objects.isNull(process.getCodeNumber()) ? 0 : process.getCodeNumber();
                    Row dataRow = sheet.createRow(rowNumber);
                    String[] data = {process.getDepartName(), process.getUserName(), ZWDateUtil.fomratterDate(process.getReportDate(), "yyyy-MM-dd"), process.getCollectingNumber().toString(),
                            process.getTouchNumber().toString(), new BigDecimal(process.getTouchRate()).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString() + "%",
                            process.getCodeNumber().toString(), new BigDecimal(process.getPenetrationRate()).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString() + "%"};
                    for (int i = 0; i < 8; i++) {
                        Cell dataCell = dataRow.createCell(i);
                        dataCell.setCellType(CellType.STRING);
                        dataCell.setCellValue(data[i]);
                        dataCell.setCellStyle(dataStyle);
                    }
                } else {
                    Row totalRow = sheet.createRow(rowNumber);
                    String touchRate = (collectingNumber == 0 ? "0.00" : (new BigDecimal((double) touchNumber / collectingNumber).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString()));
                    String penetrationRate = (touchNumber == 0 ? "0.00" : (new BigDecimal((double) codeNumber / touchNumber).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString()));
                    String[] data = {history.getDepartName(), "汇总", ZWDateUtil.fomratterDate(startDate, "yyyy-MM-dd") + "-" + ZWDateUtil.fomratterDate(endDate, "yyyy-MM-dd"),
                            String.valueOf(collectingNumber), String.valueOf(touchNumber), touchRate + "%", String.valueOf(codeNumber), penetrationRate + "%"};
                    for (int j = 0; j < 8; j++) {
                        Cell cell = totalRow.createCell(j);
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(data[j]);
                        cell.setCellStyle(dataStyle);
                    }
                    rowNumber++;
                    Row dataRow = sheet.createRow(rowNumber);
                    String[] data1 = {process.getDepartName(), process.getUserName(), ZWDateUtil.fomratterDate(process.getReportDate(), "yyyy-MM-dd"), process.getCollectingNumber().toString(),
                            process.getTouchNumber().toString(), new BigDecimal(process.getTouchRate()).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString() + "%",
                            process.getCodeNumber().toString(), new BigDecimal(process.getPenetrationRate()).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP).toString() + "%"};
                    for (int i = 0; i < 8; i++) {
                        Cell dataCell = dataRow.createCell(i);
                        dataCell.setCellType(CellType.STRING);
                        dataCell.setCellValue(data1[i]);
                        dataCell.setCellStyle(dataStyle);
                    }
                    collectingNumber = process.getCollectingNumber();
                    touchNumber = process.getTouchNumber();
                    codeNumber = process.getCodeNumber();
                    history = process;
                    userId = process.getUserId();
                }
                rowNumber++;
            }
            //最后一行汇总
            Row lastRow = sheet.createRow(rowNumber);
            String touchRate = (collectingNumber == 0 ? "0.00" : (new BigDecimal((double) touchNumber / collectingNumber).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2).toString()));
            String penetrationRate = (touchNumber == 0 ? "0.00" : (new BigDecimal((double) codeNumber / touchNumber).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2).toString()));
            String[] data = {history.getDepartName(), "汇总", ZWDateUtil.fomratterDate(startDate, "yyyy-MM-dd") + "-" + ZWDateUtil.fomratterDate(endDate, "yyyy-MM-dd"),
                    String.valueOf(collectingNumber), String.valueOf(touchNumber), touchRate + "%", String.valueOf(codeNumber), penetrationRate + "%"};
            for (int i = 0; i < 8; i++) {
                Cell dataCell = lastRow.createCell(i);
                dataCell.setCellType(CellType.STRING);
                dataCell.setCellValue(data[i]);
                dataCell.setCellStyle(dataStyle);
            }
            //合并
            String departName = "";
            int rowNum1 = 2;
            int mergeStartRow1 = 2;
            int endRow = sheet.getLastRowNum();
            for (int i = rowNum1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                String cellValue = row.getCell(0).getStringCellValue();
                if (i > 2 && i < endRow) {
                    if (!departName.equals(cellValue)) {
                        if (mergeStartRow1 < (i - 1)) {
                            CellRangeAddress dataRegion = new CellRangeAddress(mergeStartRow1, i - 1, 0, 0);
                            sheet.addMergedRegion(dataRegion);
                        }
                        mergeStartRow1 = i;
                    }
                } else if (i == endRow) {
                    if (departName.equals(cellValue)) {
                        CellRangeAddress dataRegion = new CellRangeAddress(mergeStartRow1, i, 0, 0);
                        sheet.addMergedRegion(dataRegion);
                    }
                }
                departName = cellValue;
            }
            String userName = "";
            int rowNum2 = 2;
            int mergeStartRow2 = 2;
            for (int i = rowNum2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                String cellValue = row.getCell(1).getStringCellValue();
                if (i > 2 && i < endRow) {
                    if (!userName.equals(cellValue)) {
                        if (mergeStartRow2 < (i - 1)) {
                            CellRangeAddress dataRegion = new CellRangeAddress(mergeStartRow2, i - 1, 1, 1);
                            sheet.addMergedRegion(dataRegion);
                        }
                        mergeStartRow2 = i;
                    }
                } else if (i == endRow) {
                    if (userName.equals(cellValue)) {
                        CellRangeAddress dataRegion = new CellRangeAddress(mergeStartRow2, i, 1, 1);
                        sheet.addMergedRegion(dataRegion);
                    }
                }
                userName = cellValue;
            }
            wb.write(fos);
            fos.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (Objects.nonNull(fos)) {
                    fos.close();
                }
                if (Objects.nonNull(wb)) {
                    wb.close();
                }
                list.clear();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
